from psycopg2 import connect
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import commands
import pprint
import sys

class PsqlCon:
	def __init__(self, username='postgres', dbname='pdmb', tbname='hash_url'):
		self.username = username
		self.dbname = dbname
		self.tbname = tbname
		self.con = None
		self.connectdb()

	def connectdb(self):
		self.con = connect(database=self.dbname, user=self.username)
		self.con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
		self.cur = self.con.cursor()
		self.cur.execute('SELECT version()')
		ver = self.cur.fetchone()
		print ver
		self.cur.execute("SELECT count(*) from hash_url")
		record = self.cur.fetchone()
		print 'There are ' + str(record) + ' rows in hash_url.'

	def psqlReadURLHashFromDir(self, dirname):
		files = commands.getoutput('ls ' + dirname + '/*').split('\n')
		for filename in files:
			self.psqlReadURLHashFromFile(filename)

	def psqlReadURLHashFromFile(self, filename):
		filecontent = open(filename, 'r').read()
		psqlQuery = "INSERT INTO hash_url values {0}".format(filecontent)
		print filename
		self.cur.execute(psqlQuery)
		
	def psqlSelectOneLabel(self, label):
		psqlQuery = "SELECT url, url_hash, label FROM hash_url WHERE label = '{0}'".format(label)
		self.cur.execute(psqlQuery)
		return self.cur.fetchall()

	def psqlURLSelectHash(self, url):
		psqlQuery = "SELECT url_hash FROM hash_url WHERE url = '{0}'".format(url)
		self.cur.execute(psqlQuery)
		return self.cur.fetchall()

	def psqlHashSelectURL(self, url_hash):
		psqlQuery = "SELECT url FROM hash_url WHERE url_hash = '{0}'".format(url_hash)
		self.cur.execute(psqlQuery)
		return self.cur.fetchall()

	def psqlUpdateJSHash(self, url_hash, js_hash):
		psqlQuery = "UPDATE hash_url SET js_hash = '{0}' where url_hash = '{1}';".format(js_hash, url_hash)
		self.cur.execute(psqlQuery)


if __name__=="__main__":
	con = PsqlCon()
	# con.psqlReadURLHashFromDir('../../data/TempData/URLs/')
	con.psqlReadURLHashFromFile('../../data/TempData/URLs/malurl-2013-10-29')
	# con.psqlReadURLHashFromFile('../../data/TempData/URLs/alexaustop')


